package com.x.erp.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.x.erp.model.Series;
import com.x.framework.dao.BaseDao;

@Repository
public class SeriesDao extends BaseDao {
	private final static String selectSeriesListSql = "SELECT S.*, B.*,zs FROM x_base.BASE_SERIES S join x_base.BASE_BRAND B on S.BRAND_ID = B.BRAND_ID  join  x_base.BASE_MANUFACTURER M " +
            "on  M.MANUFACTURER_ID = S.MANUFACTURER_ID left join (select A.SERIES_ID, count(1) as zs from x_base.third_series_check as A where a.SERIES_PASS=0) as N on S.SERIES_ID=N.SERIES_ID where true ";
	public List<Series> selectSeriesList(Series series) throws Exception{
		StringBuilder sb = new StringBuilder(selectSeriesListSql);
		List<Object> params = new ArrayList<Object>();
		if(series.getSeriesId() != null){
			sb.append("AND S.SERIES_ID = ? ");
			params.add(series.getSeriesId());
		}
		if(this.isString(series.getSeriesName())){
			sb.append("AND S.SERIES_NAME = ? ");
			params.add(series.getSeriesName());
		}
		if(series.getBrandId() != null){
			sb.append("AND S.BRAND_ID = ? ");
			params.add(series.getBrandId());
		}
		if(this.isString(series.getBrandName())){
			sb.append("AND B.BRAND_NAME = ? ");
			params.add(series.getBrandName());
		}
		if(this.isString(series.getBrandSeriesName())){
			sb.append("AND CONCAT(B.BRAND_NAME, S.SERIES_NAME) = ? ");
			params.add(series.getBrandSeriesName());
		}
		if(series.getManufacturerId() != null){
			sb.append("AND S.MANUFACTURER_ID = ? ");
			params.add(series.getManufacturerId());
		}
		if(this.isString(series.getManufacturerName())){
			sb.append("AND M.MANUFACTURER_NAME = ? ");
			params.add(series.getManufacturerName());
		}
        if(series.getTruePage() != null){
           this.getLimitSql(sb,series) ;
        }
		return this.queryForList(sb.toString(), Series.class, params);
	}

    /**
     * 添加
     */
    private final static String insertSeriesListSql = "INSERT INTO x_base.BASE_SERIES(SERIES_ID,SERIES_NAME,BRAND_ID,SERIES_LEVEL_NAME,SERIES_MAKER_TYPE,SERIES_LOGO) VALUES ";

    public void insertSeriesList(Series series) throws Exception{
        StringBuilder sql = new StringBuilder(insertSeriesListSql);
        List<Object> params = new ArrayList<>();
        if(series != null){
            sql.append("(?,?,?,?,?,?)") ;
            params.add(series.getSeriesId());
            params.add(series.getSeriesName());
            params.add(series.getBrandId());
            params.add(series.getSeriesLevelName());
            params.add(series.getSeriesMakerType());
            params.add(series.getSeriesLogo());
            this.execute(sql.toString(), params);
        }
    }

    /**
     * 修改
     */
    public void updateSeriesList(Series series) throws Exception{
        if(series != null){
            StringBuilder sql = new StringBuilder();
            List<Object> params = new ArrayList<>();
            sql.append("UPDATE x_base.BASE_SERIES set SERIES_NAME=?, BRAND_ID=?,SERIES_LEVEL_NAME=?,SERIES_MAKER_TYPE=? where SERIES_ID = ? ") ;
            params.add(series.getSeriesName());
            params.add(series.getBrandId());
            params.add(series.getSeriesLevelName());
            params.add(series.getSeriesMakerType());
            params.add(series.getSeriesId());
            this.execute(sql.toString(), params);
        }
    }

    /**
     * 按照series id 查询
     */
    public Series getSimpleSeries(Map<String,Object> series) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT S.*, B.*,zs FROM x_base.BASE_SERIES S join x_base.BASE_BRAND B on S.BRAND_ID = B.BRAND_ID  join  x_base.BASE_MANUFACTURER M " +
                " on  M.MANUFACTURER_ID = S.MANUFACTURER_ID left join (select A.SERIES_ID, count(1) as zs from x_base.third_series_check as A where a.SERIES_PASS=0) as N on S.SERIES_ID=N.SERIES_ID where true ") ;
        List<Object> params = new ArrayList<>();
        if(series.containsKey("seriesId")){
            params.add(series.get("seriesId"));
            sql.append(" and s.SERIES_ID = ? ") ;
        }
        if(series.containsKey("seriesName")){
            params.add(series.get("seriesName"));
            sql.append(" and s.SERIES_NAME = ? ") ;
        }
        return (Series) this.queryForObject(sql.toString(), Series.class, params);
    }

    /**
     * 分页Sql
     * @param sql
     * @param series
     */
    private void getLimitSql(StringBuilder sql, Series series) {
        if(series.getPageSize() != null && series.getPageSize() > 0) {
            sql.append(" LIMIT ").append(series.getPageIndex()-1).append(",").append(series.getPageSize());
        } else {
            sql.append(" LIMIT 5");
        }
    }
}
